
Create PROCEDURE [dbo].[Sp_Duty_Row]
    @IdHavaleh BIGINT,
    @AutoId NUMERIC(18, 0),
    @ActionCode TINYINT
AS
IF
(
    SELECT COUNT(Price_Row) Icount
    FROM Tmp_Over_Under_Row WITH (NOLOCK)
    WHERE Price_Row = 1
          AND ShowPrice = 1
          AND AutoId = @AutoId
) > 0
BEGIN

    DECLARE @TotPrice MONEY;
    SELECT @TotPrice = ISNULL(T2.SumRow, 0) - ABS(TotOverUnder)
    FROM
    (
        SELECT Tmp_Over_Under_Row.AutoId,
               (SUM(   CASE
                           WHEN dbo.Tmp_Over_Under_Row.Type = 1 THEN
                               Price
                           ELSE
                               0
                       END
                   ) - SUM(   CASE
                                  WHEN Tmp_Over_Under_Row.Type = 0 THEN
                                      Price
                                  ELSE
                                      0
                              END
                          )
               ) TotOverUnder
        FROM dbo.Tmp_Over_Under_Row WITH (NOLOCK)
            INNER JOIN dbo.Over_Under WITH (NOLOCK)
                ON Over_Under.AutoId = Code_Over_Under
        WHERE dbo.Tmp_Over_Under_Row.AutoId = @AutoId
              AND dbo.Over_Under.Duty_Flag = 1
              AND Action_Code = @ActionCode
        GROUP BY Tmp_Over_Under_Row.AutoId
    ) AS T1
        INNER JOIN
        (
            SELECT TmpHeadH.Id_Havaleh,
                   AutoId,
                   CASE
                       WHEN Kind_Flag IN ( 5, 9, 7, 0, 6 ) THEN
                           ROUND(K_Qty1 * Price_Forosh, 0)
                       WHEN Kind_Flag IN ( 2, 3, 4, 8 ) THEN
                           ROUND(K_Qty1 * Price_KHarid, 0)
                   END AS SumRow
            FROM TmpHeadH WITH (NOLOCK)
                INNER JOIN TmpHavaleh WITH (NOLOCK)
                    ON TmpHavaleh.Id_Havaleh = TmpHeadH.Id_Havaleh
            WHERE dbo.TmpHavaleh.AutoId = @AutoId
                  AND Takhfif_Flag = 1
        ) AS T2
            ON T1.AutoId = T2.AutoId;


    IF ISNULL(@TotPrice, 0) > 0
    BEGIN
        UPDATE dbo.Tmp_Over_Under_Row
        SET Price = @TotPrice * CAST([Percent] AS DEC(16, 6)) / 100
		--ROUND(@TotPrice * [Percent] / 100, 0)
        FROM Tmp_Over_Under_Row WITH (NOLOCK)

            --UPDATE dbo.Tmp_Over_Under_Row SET price =ceiling( (@TotPrice * [percent])/100) FROM Tmp_Over_Under_Row WITH (NOLOCK)
            INNER JOIN dbo.Over_Under WITH (NOLOCK)
                ON Over_Under.AutoId = Code_Over_Under
            INNER JOIN TmpHavaleh WITH (NOLOCK)
                ON TmpHavaleh.AutoId = Tmp_Over_Under_Row.AutoId
        WHERE Price_Row = 1
              AND Tmp_Over_Under_Row.ShowPrice = 1
              AND TmpHavaleh.AutoId = @AutoId
              AND Tmp_Over_Under_Row.Type = 1;
    END;
END;

